<?php
/**
 * Created by PhpStorm.
 * User: locove
 * Date: 15-4-2
 * Time: 下午5:41
 */
class sirens
{
    public $skus = null;
    public $request = null;
    public $skus_str = null;
    public $yesterday = null;
    public $old = null;

    public function doSirens()
    {
        if(empty($this->skus)) return;

        $this->request = date("Y-m-01",$_SERVER['REQUEST_TIME']);
        $this->today = date("Y-m-d",$_SERVER['REQUEST_TIME']);
        $this->yesterday = date("Y-m-d",$_SERVER['REQUEST_TIME'] - 86400);
        $this->skus_str = "'".join("','",$this->skus)."'";

        $this->doAction();
    }

    public function doAction()
    {
        //***************************************************计算实际库存********************************************
        $time1 = time();
        $sql = "SELECT
                    UPPER (`sku`) `sku`,SUM(`warehouse_quantity`) AS `num`
                FROM
                    `warehouseorders`
                WHERE
                    `warehouse_date` >= '{$this->request}'
                AND sku IN ({$this->skus_str})
                GROUP BY `sku`";

        $was = DB::query(Database::SELECT,$sql)->execute('newerp')->as_array('sku','num');

        $sql = "SELECT
                     UPPER (`sku`) `sku`,SUM(`quantity`) AS `num`
                FROM
                    `deliveryorders`
                WHERE
                    `delivery_date` >= '{$this->request}'
                AND sku IN ({$this->skus_str})
                GROUP BY `sku`";
        $des = DB::query(Database::SELECT,$sql)->execute('newerp')->as_array('sku','num');

        $sql = "SELECT
                    UPPER (`sku`) `sku`,`quantity` AS `num`
                FROM
                    `inventories`
                WHERE
                    `inventory_date` = '{$this->request}'
                AND sku IN ({$this->skus_str})
                GROUP BY `sku`";

        $ins = DB::query(Database::SELECT,$sql)->execute('newerp')->as_array('sku','num');

        $actuals = array();
        foreach($this->skus as $sku)
        {
            $in = isset($ins[strtoupper($sku)])?$ins[strtoupper($sku)]:0;
            $wa = isset($was[strtoupper($sku)])?$was[strtoupper($sku)]:0;
            $de = isset($des[strtoupper($sku)])?$des[strtoupper($sku)]:0;

            $actuals[$sku] = $in + $wa - $de;
        }

        unset($was);
        unset($des);
        unset($ins);
        ////print_r($actuals);
        //echo '实际库存计算所需时间：'.(time()-$time1)."<br/>";

        //***************************************************计算占在途********************************************
        $time2=time();
        $holds = array();
        $sql = "SELECT
                  upper(erp_orders_products.orders_sku) AS sku,
                  SUM( erp_orders_products.item_count ) AS hold
               FROM
                  erp_orders_products
               JOIN
                  erp_orders
               ON erp_orders_products.erp_orders_id = erp_orders.erp_orders_id
               AND erp_orders.orders_type!=-1
               AND erp_orders.orders_type NOT IN (41,42,49,44)
               AND erp_orders.orders_status IN (1,3,4,8)
               AND erp_orders_products.orders_sku <> ''
               AND erp_orders_products.orders_sku IN ({$this->skus_str})
               GROUP BY erp_orders_products.orders_sku";

        $res = DB::query(Database::SELECT,$sql)->execute('readonlyerp')->as_array('sku','hold');

        $sku_have = array_keys($res);
        $sku_zeros = array_diff($this->skus,$sku_have);
        foreach($sku_zeros as $sku_zero)
        {
            $holds[$sku_zero] = 0;
        }

        $sku_string = "'".join("','",$sku_have)."'";

        if($res){
            $print_n = $res;

            $eop_op_ids_sql = "SELECT upper(eop.orders_sku) AS sku,eo.`erp_orders_id`,eop.orders_products_id FROM erp_orders eo, erp_orders_products eop
                    WHERE eo.erp_orders_id = eop.erp_orders_id AND eo.orders_status =4 AND eop.orders_sku in ({$sku_string})";

            $eop_op_ids = DB::query(Database::SELECT,$eop_op_ids_sql)->execute('readonlyerp')->as_array();

            if($eop_op_ids){
                $eop_skus = $unpacks = array();
                $prints_str = '';
                foreach($eop_op_ids as $op)
                {
                    $op_sku = $op['sku'];
                    if(!$op_sku)continue;

                    if(empty($eop_skus))
                    {
                        $eop_skus[] = $op_sku;
                    }else{
                        if(!in_array($op_sku,$eop_skus))
                            $eop_skus[] = $op_sku;
                    }

                    if(isset($unpacks[$op_sku]))
                    {
                        if(!in_array($op['orders_products_id'],$unpacks[$op_sku]))
                            $unpacks[$op_sku][] = $op['orders_products_id'];
                    }else{
                        $unpacks[$op_sku][] = $op['orders_products_id'];
                    }

                    $prints_str .= $prints_str?",'".$op['orders_products_id']."'":"'".$op['orders_products_id']."'";
                }

                //出库表没有出库的sku
                $sku_no_del = array_diff($sku_have,$eop_skus);

                if($sku_no_del)
                {
                    foreach($sku_no_del as $_sku_no)
                    {
                        if(isset($print_n[$_sku_no]))
                            $holds[$_sku_no] = $print_n[$_sku_no];
                        else
                            $holds[$_sku_no] = 0;
                    }
                }

                $sql = "SELECT upper(`sku`) as sku,orders_products_id,`quantity` FROM `deliveryorders` WHERE `orders_products_id`  IN ({$prints_str})";

                $prints = DB::query(Database::SELECT,$sql)->execute('newerp')->as_array();

                $qulity = array();
                foreach($prints as $print)
                {
                    $qulity[$print['sku']][$print['orders_products_id']] = $print['quantity'];
                }
                unset($prints);

                $print_del = array();

                foreach($unpacks as $sku_temp=>$_print)
                {
                    foreach($_print as $_eod)
                    {
                        if(isset($qulity[$sku_temp][$_eod])){
                            if(isset($print_del[$sku_temp]))
                                $print_del[$sku_temp] += $qulity[$sku_temp][$_eod];
                            else
                                $print_del[$sku_temp] = $qulity[$sku_temp][$_eod];
                        }
                    }
                }

                $print_num = array();
                foreach($sku_have as $_sku)
                {
                    if(isset($print_del[$_sku]))
                    {
                        $print_num[$_sku] = $print_n[$_sku]-$print_del[$_sku];
                    }else{
                        $print_num[$_sku] = $print_n[$_sku];
                    }
                }

                unset($unpacks);
                unset($qulity);
                $holds = array_merge($holds,$print_num);
            }else{
                foreach($res as $_sku=>$_re)
                {
                    $holds[$_sku] = $_re;
                }
            }
        }
        //print_r($holds);
        //echo '占在途库存计算所需时间：'.(time()-$time2)."<br/>";

        //***************************************************计算在途库存********************************************
        $time3=time();
        $sql = "select upper(sku) as sku,SUM(quantity-ware_quantity) as onway
                from  purchaseorder_details  where sku IN ({$this->skus_str})
                and  status<70
                and  details_date >= DATE_ADD(NOW(),INTERVAL -90 DAY) group by sku";

        $onways = DB::query(Database::SELECT,$sql)->execute('newerp')->as_array('sku','onway');
        //print_r($onways);
        //echo '在途库存计算所需时间：'.(time()-$time3)."<br/>";

        //***************************************************计算日均销量********************************************
        $time4 = time();
        if($this->old) {
            $daysales = self::getOldDaySale($this->skus_str);
        }else {
            $sql = "SELECT `sku`,SUM(`daysale`) `daysale`
                    FROM `sku_sales`
                    WHERE `count_date`='{$this->yesterday}'
                    AND `sku` IN({$this->skus_str})
                    GROUP BY `sku`";
            $daysales = DB::query(Database::SELECT,$sql)->execute('newerp')->as_array('sku','daysale');
        }
        //echo $sql;print_r($daysales);exit;
        //echo '日均销量获取所需时间：'.(time()-$time4)."<br/>";
        //***************************************************计算交期********************************************
        $time5 = time();
        $sql = "SELECT `sku`,`su_id`,`ldt_sec`,`def_sec`
                FROM `ldt_ord`
                WHERE `sku` IN({$this->skus_str})";

        $leadtimes = DB::query(Database::SELECT,$sql)->execute('newerp')->as_array();
        $ldts = array();
        foreach($leadtimes as $l)
        {
            $ldts[$l['sku']][$l['su_id']]['ldt_sec'] = $l['ldt_sec'];
            $ldts[$l['sku']][$l['su_id']]['def_sec'] = $l['def_sec'];
        }
        $leadtimes = $ldts;
        unset($ldts);

        //print_r($leadtimes);
        //echo '交期获取所需时间：'.(time()-$time5)."<br/>";
        //**********************************************最后计算插入到临时表***********************************************
        $cols = array('sku', 'actual', 'hold', 'onway', 'safe', 'sup_sec', 'add_time', 'old');
        $fg = false;
        $re = DB::insert("sirens")->columns($cols);
        $reb = DB::insert("sirens_best")->columns(array('sku','su_id','ldt_sec','do_sec','def_sec'));

        $paras = array('actual','hold','onway','daysale');

        $now = date("Y-m-d H:i:s",time());
        $wave = 3;
        foreach($this->skus as $sku)
        {
            foreach($paras as $para)
            {
                $$para = isset(${$para.'s'}[$sku])?${$para.'s'}[$sku]:0;
            }

            if(${$paras[3]}<=0)continue;

            if(isset($leadtimes[$sku]))
            {
                $in_fg = 0;
                foreach($leadtimes[$sku] as $ldt)
                {
                    $ldt_sec = isset($ldt['def_sec']) && $ldt['def_sec'] > 0 ? $ldt['def_sec'] : $ldt['ldt_sec'];
                    $sup_days = round(((float)${$paras[0]}-(float)${$paras[1]}+(float)${$paras[2]})/((float)${$paras[3]}),6)-(float)$wave;

                    $sup_days = $sup_days * 86400;

                    $do_sec = $sup_days - $ldt_sec;

                    if($do_sec>604800)continue;

                    $fg = true;
                    $in_fg = 1;
                    //只要这个sku是最近7天需要下单的，那么有关这个sku的所有都存储起来
                    foreach($leadtimes[$sku] as $su_id=>$ldt)
                    {
                        $ldt_sec = isset($ldt['def_sec']) && $ldt['def_sec'] > 0 ? $ldt['def_sec'] : $ldt['ldt_sec'];
                        $sup_days = round(((float)${$paras[0]}-(float)${$paras[1]}+(float)${$paras[2]})/((float)${$paras[3]}),6)-(float)$wave;

                        $sup_days = $sup_days * 86400;

                        $do_sec = $sup_days - $ldt_sec;

                        $reb->values(array($sku,$su_id,$ldt['ldt_sec'],$do_sec,$ldt['def_sec']));
                    }
                    break;
                }
                if($in_fg)$re->values(array($sku,${$paras[0]},${$paras[1]},${$paras[2]},${$paras[3]}*$wave,$sup_days,$now, $this->old?1:0));
            }
        }

        //echo strlen($re->compile('newerp'))."<br/>";

        if($fg)
        {
            $re->execute('newerp');
            $reb->execute('newerp');
        }
        foreach($paras as $para)
        {
            unset(${$para.'s'});
        }

        $this->beforDone();
    }

    //更新预警表，如果是最近7天包含周日，则提前一天
    public function beforDone () {
        function getSunday(){
            $re = array();

            for($i = 0; $i<7; $i ++) {
                $that = date("Y-m-d", strtotime("+$i day"));
                if(date('w',strtotime($that)) == 0) {
                    $re = array('b_sec'=>strtotime($that), 'e_sec'=>strtotime($that." 23:59:59"));
                    break;
                }
            }

            return $re;
        }

        $now_sec = time();
        $goals = getSunday();

        $sql = "SELECT `id` FROM `sirens_best` WHERE `do_sec` + {$now_sec} >= {$goals['b_sec']} AND `do_sec` + {$now_sec} < {$goals['e_sec']}";
        $ids = DB::query(Database::SELECT, $sql)->execute('newerp')->as_array(NULL, 'id');

        if(empty($ids))return;

        $ids_str = '';
        foreach($ids as $id) {
            $ids_str .= $ids_str !=''?','.$id:$id;
        }

        $sql = "UPDATE `sirens_best` set `do_sec` = (`do_sec`-86400) WHERE `id` IN ({$ids_str})";

        DB::query(Database::UPDATE, $sql)->execute('newerp');
    }

    public function actuals_copy()
    {
        $sql ="SELECT
                  upper( `sku` ) AS `sku`, sum(`num`) AS `inventory`
               FROM (
                    SELECT
                        `warehouseorders`.`sku` AS `sku`,
                        `warehouseorders`.`warehouse_quantity` AS `num`
                    FROM
                        `warehouseorders`
                    WHERE
                        `warehouseorders`.`warehouse_date` >= '{$this->request}'
                    UNION ALL
                        SELECT
                            `deliveryorders`.`sku` AS `sku` ,
                             - ( `deliveryorders`.`quantity` ) AS `num`
                        FROM
                            `deliveryorders`
                        WHERE
                            `deliveryorders`.`delivery_date` >= '{$this->request}'
                    UNION ALL
                        SELECT
                            `inventories`.`sku` AS `sku` , `inventories`.`quantity` AS `num`
                        FROM
                            `inventories`
                        WHERE
                            `inventories`.`inventory_date` = '{$this->request}'
               )a
               WHERE sku IN ({$this->skus_str})
               GROUP BY sku";

        return DB::query(Database::SELECT,$sql)->execute('newerp')->as_array();
    }

    //从老erp获取日均销量数据
    static function getOldDaySale($skus_str = NULL)
    {
        if(!empty($skus_str)){

            $sql_string = "select upper(sku) as sku,daysales as daysale from product_attached where sku in ({$skus_str})";

            return DB::query(Database::SELECT,$sql_string)
                ->execute('readonlyerp')
                ->as_array('sku', 'daysale');
        }
    }
}